package com.wysql.ascost.controller.admin;

import com.wysql.ascost.model.info.*;
import com.wysql.ascost.service.info.InfoProjectbudgetCsService;
import com.wysql.ascost.service.info.InfoProjectbudgetJsService;
import com.wysql.ascost.service.info.InfoProjectbudgetService;
import com.wysql.ascost.util.SystemExportTools;
import lombok.RequiredArgsConstructor;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @author 23562
 */
@Controller
@RequestMapping("AgencyStyleExportController")
@RequiredArgsConstructor
public class AgencyStyleExportController {

    @Value("${file.templatePath}")
    private String templatePaths;

    @Autowired
    private InfoProjectbudgetService infoProjectbudgetService;

    @Autowired
    private InfoProjectbudgetCsService infoProjectbudgetCsService;

    @Autowired
    private InfoProjectbudgetJsService infoProjectbudgetJsService;

    /**
     * 可研环节导出
     *
     * @param request
     * @param response
     * @throws IOException
     */
    @GetMapping("agencyStyleExport4pro")
    public void agencyStyleExport1(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String isconfirm = request.getParameter("isconfirm");
        // 创建sheet
        String templatePath = templatePaths + "/projectBudget.xls";
        FileInputStream fileInputStream = new FileInputStream(templatePath);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFFont font = workbook.createFont();

        Map<Integer, HSSFCellStyle> styleMap = SystemExportTools.indexStyle(workbook, font);
        Map<Integer, HSSFCellStyle> styleMapRed = SystemExportTools.indexStyleRed(workbook, font);

        //创建行
        HSSFSheet sheet = workbook.getSheetAt(0);
        InfoProjectbudgetExample infoProjectbudgetExample = new InfoProjectbudgetExample();
        infoProjectbudgetExample.createCriteria().andIsconfirmEqualTo(Integer.valueOf(isconfirm));
        List<InfoProjectbudget> infoProjectbudgets = infoProjectbudgetService.selectByExample(infoProjectbudgetExample);

        //常见单元格并写入数据
        for (int i = 0; i < infoProjectbudgets.size(); i++) {

            Row row = sheet.createRow((i + 1));
            InfoProjectbudget project = infoProjectbudgets.get(i);
            //创建时间
            Cell cell = row.createCell(0);
            cell.setCellValue(project.getBookdate());
            cell.setCellStyle(styleMap.get(0));
            //工程类别（1基建类2业扩类）
            cell = row.createCell(1);
            cell.setCellValue(project.getType());
            cell.setCellStyle(styleMap.get(0));
            //工程名称
            cell = row.createCell(2);
            cell.setCellValue(project.getProname());
            cell.setCellStyle(styleMap.get(0));
            //建筑工程费
            cell = row.createCell(3);
            cell.setCellValue(project.getJzgcf()==null?0:project.getJzgcf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //安装工程费
            cell = row.createCell(4);
            cell.setCellValue(project.getAzgcf()==null?0:project.getAzgcf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //设备购置费
            cell = row.createCell(5);
            cell.setCellValue(project.getSbgzf()==null?0:project.getSbgzf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //其他费
            cell = row.createCell(6);
            cell.setCellValue(project.getQtf()==null?0:project.getQtf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //总投资
            cell = row.createCell(7);
            cell.setCellValue(project.getTotal()==null?0:project.getTotal().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //建设场地征用及清理费
            cell = row.createCell(8);
            cell.setCellValue(project.getJscdzyjglf()==null?0:project.getJscdzyjglf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目建设管理费
            cell = row.createCell(9);
            cell.setCellValue(project.getXmjsglf()==null?0:project.getXmjsglf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目管理经费
            cell = row.createCell(10);
            cell.setCellValue(project.getXmgljf()==null?0:project.getXmgljf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //工程监理费
            cell = row.createCell(11);
            cell.setCellValue(project.getGcjlf()==null?0:project.getGcjlf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //施工费
            cell = row.createCell(12);
            cell.setCellValue(project.getSgf()==null?0:project.getSgf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目前期工作费
            cell = row.createCell(13);
            cell.setCellValue(project.getXmqqgzf()==null?0:project.getXmqqgzf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //勘察费
            cell = row.createCell(14);
            cell.setCellValue(project.getKcf()==null?0:project.getKcf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //设计费
            cell = row.createCell(15);
            cell.setCellValue(project.getSjf()==null?0:project.getSjf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //设计文件评审费
            cell = row.createCell(16);
            cell.setCellValue(project.getSjwjpsf()==null?0:project.getSjwjpsf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目后评价费
            cell = row.createCell(17);
            cell.setCellValue(project.getXmhpjf()==null?0:project.getXmhpjf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //建设期贷款利息
            cell = row.createCell(18);
            cell.setCellValue(project.getJsqdklx()==null?0:project.getJsqdklx().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //工程保险费
            cell = row.createCell(19);
            cell.setCellValue(project.getGcbxf()==null?0:project.getGcbxf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //施工过程造价咨询及竣工结算审核费
            cell = row.createCell(20);
            cell.setCellValue(project.getJgjsshf()==null?0:project.getJgjsshf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //生产准备费
            cell = row.createCell(21);
            cell.setCellValue(project.getSczbf()==null?0:project.getSczbf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //带电作业费
            cell = row.createCell(22);
            cell.setCellValue(project.getDdzyf()==null?0:project.getDdzyf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //甲供材合计
            cell = row.createCell(23);
            cell.setCellValue(project.getJgchjje()==null?0:project.getJgchjje().doubleValue());
            cell.setCellStyle(styleMap.get(1));


            //工程类别
            cell = row.createCell(24);
            cell.setCellValue(project.getCreatetype());
            cell.setCellStyle(styleMap.get(0));
            //是否入库
            cell = row.createCell(25);
            cell.setCellValue(project.getIsconfirm());
            cell.setCellStyle(styleMap.get(0));
            //工程书日期
            cell = row.createCell(26);
            cell.setCellValue(project.getBookdate());
            cell.setCellStyle(styleMap.get(0));
            //导入文件名称
            cell = row.createCell(27);
            cell.setCellValue(project.getFilename());
            cell.setCellStyle(styleMap.get(0));
            //入库确认时间
            cell = row.createCell(28);
            InfoProjectbudget infoProjectbudget = project;
            System.out.println(Objects.isNull(cell));
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//            cell.setCellValue(Strings.isBlank(project.getConfirmdate().toString())?null:project.getConfirmdate());
            cell.setCellValue(project.getConfirmdate() == null ? " " : formatter.format(project.getConfirmdate()));
            cell.setCellStyle(styleMap.get(0));

        }

        String fileName = "111.xls";
        SystemExportTools.commonDownload(request, response, workbook, fileName);
    }

    /**
     * 可研环节导出--用于项目编号绑定
     *
     * @param request
     * @param response
     * @throws IOException
     */
    @GetMapping("agencyStyleExport4pronum")
    public void agencyStyleExport4pronum(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String isconfirm = request.getParameter("isconfirm");
        // 创建sheet
        String templatePath = templatePaths + "/projectBudget4num.xls";
        FileInputStream fileInputStream = new FileInputStream(templatePath);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFFont font = workbook.createFont();

        Map<Integer, HSSFCellStyle> styleMap = SystemExportTools.indexStyle(workbook, font);
        Map<Integer, HSSFCellStyle> styleMapRed = SystemExportTools.indexStyleRed(workbook, font);

        //创建行
        HSSFSheet sheet = workbook.getSheetAt(0);
        InfoProjectbudgetExample infoProjectbudgetExample = new InfoProjectbudgetExample();
//        infoProjectbudgetExample.createCriteria().andIsconfirmEqualTo(Integer.valueOf(isconfirm));
        List<InfoProjectbudget> infoProjectbudgets = infoProjectbudgetService.selectByExample(infoProjectbudgetExample);

        //常见单元格并写入数据
        for (int i = 0; i < infoProjectbudgets.size(); i++) {
            Row row = sheet.createRow((i + 1));
            InfoProjectbudget project = infoProjectbudgets.get(i);
            //创建时间
            Cell cell = row.createCell(0);
            cell.setCellValue(project.getPronum());
            cell.setCellStyle(styleMap.get(0));
            //工程类别（1基建类2业扩类）
            cell = row.createCell(1);
            cell.setCellValue(project.getProname());
            cell.setCellStyle(styleMap.get(0));
            //工程名称
            cell = row.createCell(2);
            cell.setCellValue(project.getType());
            cell.setCellStyle(styleMap.get(0));
            //工程书日期
            cell = row.createCell(3);
            cell.setCellValue(project.getBookdate());
            cell.setCellStyle(styleMap.get(0));
            //导入文件名称
            cell = row.createCell(4);
            cell.setCellValue(project.getFilename());
            cell.setCellStyle(styleMap.get(0));
            //是否入库
            cell = row.createCell(5);
            cell.setCellValue(project.getIsconfirm());
            cell.setCellStyle(styleMap.get(0));
            //入库确认时间
            cell = row.createCell(6);
            InfoProjectbudget infoProjectbudget = project;
            System.out.println(Objects.isNull(cell));
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//            cell.setCellValue(Strings.isBlank(project.getConfirmdate().toString())?null:project.getConfirmdate());
            cell.setCellValue(project.getConfirmdate() == null ? " " : formatter.format(project.getConfirmdate()));
            cell.setCellStyle(styleMap.get(0));
            //所属环节
            cell = row.createCell(7);
            cell.setCellValue("可研");
            cell.setCellStyle(styleMap.get(0));

        }

        String fileName = "111.xls";
        SystemExportTools.commonDownload(request, response, workbook, fileName);
    }

    private void setDoubleFormatStyle(HSSFWorkbook workbook, Map<Integer, HSSFCellStyle> styleMap, Cell cell) {
        HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat();
        HSSFCellStyle hssfCellStyle = styleMap.get(0);
        hssfCellStyle.setDataFormat(format.getFormat("#,##0.00"));
        cell.setCellStyle(hssfCellStyle);
    }

    /**
     * 初设环节导出
     *
     * @param request
     * @param response
     * @throws IOException
     */
    @GetMapping("agencyStyleExport4proCs")
    public void agencyStyleExport4proCs(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String isconfirm = request.getParameter("isconfirm");
        // 创建sheet
        String templatePath = templatePaths + "/projectBudget.xls";
        FileInputStream fileInputStream = new FileInputStream(templatePath);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFFont font = workbook.createFont();

        Map<Integer, HSSFCellStyle> styleMap = SystemExportTools.indexStyle(workbook, font);
        Map<Integer, HSSFCellStyle> styleMapRed = SystemExportTools.indexStyleRed(workbook, font);

        //创建行
        HSSFSheet sheet = workbook.getSheetAt(0);
        InfoProjectbudgetCsExample infoProjectbudgetExample = new InfoProjectbudgetCsExample();
        infoProjectbudgetExample.createCriteria().andIsconfirmEqualTo(Integer.valueOf(isconfirm));
        List<InfoProjectbudgetCs> infoProjectbudgets = infoProjectbudgetCsService.selectByExample(infoProjectbudgetExample);

        //常见单元格并写入数据
        for (int i = 0; i < infoProjectbudgets.size(); i++) {

            Row row = sheet.createRow((i + 1));
            InfoProjectbudgetCs project = infoProjectbudgets.get(i);
            //创建时间
            Cell cell = row.createCell(0);
            cell.setCellValue(project.getBookdate());
            cell.setCellStyle(styleMap.get(0));
            //工程类别（1基建类2业扩类）
            cell = row.createCell(1);
            cell.setCellValue(project.getType());
            cell.setCellStyle(styleMap.get(0));
            //工程名称
            cell = row.createCell(2);
            cell.setCellValue(project.getProname());
            cell.setCellStyle(styleMap.get(0));

            //建筑工程费
            cell = row.createCell(3);
            cell.setCellValue(project.getJzgcf()==null?0:project.getJzgcf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //安装工程费
            cell = row.createCell(4);
            cell.setCellValue(project.getAzgcf()==null?0:project.getAzgcf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //设备购置费
            cell = row.createCell(5);
            cell.setCellValue(project.getSbgzf()==null?0:project.getSbgzf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //其他费
            cell = row.createCell(6);
            cell.setCellValue(project.getQtf()==null?0:project.getQtf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //总投资
            cell = row.createCell(7);
            cell.setCellValue(project.getTotal()==null?0:project.getTotal().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //建设场地征用及清理费
            cell = row.createCell(8);
            cell.setCellValue(project.getJscdzyjglf()==null?0:project.getJscdzyjglf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目建设管理费
            cell = row.createCell(9);
            cell.setCellValue(project.getXmjsglf()==null?0:project.getXmjsglf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目管理经费
            cell = row.createCell(10);
            cell.setCellValue(project.getXmgljf()==null?0:project.getXmgljf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //工程监理费
            cell = row.createCell(11);
            cell.setCellValue(project.getGcjlf()==null?0:project.getGcjlf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //施工费
            cell = row.createCell(12);
            cell.setCellValue(project.getSgf()==null?0:project.getSgf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目前期工作费
            cell = row.createCell(13);
            cell.setCellValue(project.getXmqqgzf()==null?0:project.getXmqqgzf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //勘察费
            cell = row.createCell(14);
            cell.setCellValue(project.getKcf()==null?0:project.getKcf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //设计费
            cell = row.createCell(15);
            cell.setCellValue(project.getSjf()==null?0:project.getSjf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //设计文件评审费
            cell = row.createCell(16);
            cell.setCellValue(project.getSjwjpsf()==null?0:project.getSjwjpsf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目后评价费
            cell = row.createCell(17);
            cell.setCellValue(project.getXmhpjf()==null?0:project.getXmhpjf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //建设期贷款利息
            cell = row.createCell(18);
            cell.setCellValue(project.getJsqdklx()==null?0:project.getJsqdklx().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //工程保险费
            cell = row.createCell(19);
            cell.setCellValue(project.getGcbxf()==null?0:project.getGcbxf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //施工过程造价咨询及竣工结算审核费
            cell = row.createCell(20);
            cell.setCellValue(project.getJgjsshf()==null?0:project.getJgjsshf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //生产准备费
            cell = row.createCell(21);
            cell.setCellValue(project.getSczbf()==null?0:project.getSczbf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //带电作业费
            cell = row.createCell(22);
            cell.setCellValue(project.getDdzyf()==null?0:project.getDdzyf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //甲供材
            cell = row.createCell(23);
            cell.setCellValue(project.getJgchjje()==null?0:project.getJgchjje().doubleValue());
            cell.setCellStyle(styleMap.get(1));

            //工程类别
            cell = row.createCell(24);
            cell.setCellValue(project.getCreatetype());
            cell.setCellStyle(styleMap.get(0));
            //是否入库
            cell = row.createCell(25);
            cell.setCellValue(project.getIsconfirm());
            cell.setCellStyle(styleMap.get(0));
            //工程书日期
            cell = row.createCell(26);
            cell.setCellValue(project.getBookdate());
            cell.setCellStyle(styleMap.get(0));
            //导入文件名称
            cell = row.createCell(27);
            cell.setCellValue(project.getFilename());
            cell.setCellStyle(styleMap.get(0));
            //入库确认时间
            cell = row.createCell(28);
            InfoProjectbudgetCs infoProjectbudget = project;
            System.out.println(Objects.isNull(cell));
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//            cell.setCellValue(Strings.isBlank(project.getConfirmdate().toString())?null:project.getConfirmdate());
            cell.setCellValue(project.getConfirmdate() == null ? " " : formatter.format(project.getConfirmdate()));
            cell.setCellStyle(styleMap.get(0));

        }

        String fileName = "222.xls";
        SystemExportTools.commonDownload(request, response, workbook, fileName);
    }

    /**
     * 可研环节导出--用于项目编号绑定
     *
     * @param request
     * @param response
     * @throws IOException
     */
    @GetMapping("agencyStyleExport4proCsnum")
    public void agencyStyleExport4proCsnum(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String isconfirm = request.getParameter("isconfirm");
        // 创建sheet
        String templatePath = templatePaths + "/projectBudget4num.xls";
        FileInputStream fileInputStream = new FileInputStream(templatePath);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFFont font = workbook.createFont();

        Map<Integer, HSSFCellStyle> styleMap = SystemExportTools.indexStyle(workbook, font);
        Map<Integer, HSSFCellStyle> styleMapRed = SystemExportTools.indexStyleRed(workbook, font);

        //创建行
        HSSFSheet sheet = workbook.getSheetAt(0);
        InfoProjectbudgetCsExample infoProjectbudgetCsExample = new InfoProjectbudgetCsExample();
//        infoProjectbudgetCsExample.createCriteria().andIsconfirmEqualTo(Integer.valueOf(isconfirm));
        List<InfoProjectbudgetCs> infoProjectbudgetCss = infoProjectbudgetCsService.selectByExample(infoProjectbudgetCsExample);

        //常见单元格并写入数据
        for (int i = 0; i < infoProjectbudgetCss.size(); i++) {
            Row row = sheet.createRow((i + 1));
            InfoProjectbudgetCs project = infoProjectbudgetCss.get(i);
            //创建时间
            Cell cell = row.createCell(0);
            cell.setCellValue(project.getPronum());
            cell.setCellStyle(styleMap.get(0));
            //工程类别（1基建类2业扩类）
            cell = row.createCell(1);
            cell.setCellValue(project.getProname());
            cell.setCellStyle(styleMap.get(0));
            //工程名称
            cell = row.createCell(2);
            cell.setCellValue(project.getType());
            cell.setCellStyle(styleMap.get(0));
            //工程书日期
            cell = row.createCell(3);
            cell.setCellValue(project.getBookdate());
            cell.setCellStyle(styleMap.get(0));
            //导入文件名称
            cell = row.createCell(4);
            cell.setCellValue(project.getFilename());
            cell.setCellStyle(styleMap.get(0));
            //是否入库
            cell = row.createCell(5);
            cell.setCellValue(project.getIsconfirm());
            cell.setCellStyle(styleMap.get(0));
            //入库确认时间
            cell = row.createCell(6);
            InfoProjectbudgetCs infoProjectbudgetCs = project;
            System.out.println(Objects.isNull(cell));
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//            cell.setCellValue(Strings.isBlank(project.getConfirmdate().toString())?null:project.getConfirmdate());
            cell.setCellValue(project.getConfirmdate() == null ? " " : formatter.format(project.getConfirmdate()));
            cell.setCellStyle(styleMap.get(0));
            //所属环节
            cell = row.createCell(7);
            cell.setCellValue("初设");
            cell.setCellStyle(styleMap.get(0));

        }

        String fileName = "111.xls";
        SystemExportTools.commonDownload(request, response, workbook, fileName);
    }

    /**
     * 初设环节导出
     *
     * @param request
     * @param response
     * @throws IOException
     */
    @GetMapping("agencyStyleExport4proJs")
    public void agencyStyleExport4proJs(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String isconfirm = request.getParameter("isconfirm");
        // 创建sheet
        String templatePath = templatePaths + "/projectBudget.xls";
        FileInputStream fileInputStream = new FileInputStream(templatePath);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFFont font = workbook.createFont();

        Map<Integer, HSSFCellStyle> styleMap = SystemExportTools.indexStyle(workbook, font);
        Map<Integer, HSSFCellStyle> styleMapRed = SystemExportTools.indexStyleRed(workbook, font);

        //创建行
        HSSFSheet sheet = workbook.getSheetAt(0);
        InfoProjectbudgetJsExample infoProjectbudgetExample = new InfoProjectbudgetJsExample();
        infoProjectbudgetExample.createCriteria().andIsconfirmEqualTo(Integer.valueOf(isconfirm));
        List<InfoProjectbudgetJs> infoProjectbudgets = infoProjectbudgetJsService.selectByExample(infoProjectbudgetExample);

        //常见单元格并写入数据
        for (int i = 0; i < infoProjectbudgets.size(); i++) {

            Row row = sheet.createRow((i + 1));
            InfoProjectbudgetJs project = infoProjectbudgets.get(i);
            //创建时间
            Cell cell = row.createCell(0);
            cell.setCellValue(String.valueOf(project.getBookdate()));
            cell.setCellStyle(styleMap.get(0));
            //工程类别（1基建类2业扩类）
            cell = row.createCell(1);
            cell.setCellValue(String.valueOf(project.getType()));
            cell.setCellStyle(styleMap.get(0));
            //工程名称
            cell = row.createCell(2);
            cell.setCellValue(String.valueOf(project.getProname()));
            cell.setCellStyle(styleMap.get(0));
            //建筑工程费
            cell = row.createCell(3);
            cell.setCellValue(project.getJzgcf()==null?0:project.getJzgcf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //安装工程费
            cell = row.createCell(4);
            cell.setCellValue(project.getAzgcf()==null?0:project.getAzgcf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //设备购置费
            cell = row.createCell(5);
            cell.setCellValue(project.getSbgzf()==null?0:project.getSbgzf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //其他费
            cell = row.createCell(6);
            cell.setCellValue(project.getQtf()==null?0:project.getQtf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //总投资
            cell = row.createCell(7);
            cell.setCellValue(project.getTotal()==null?0:project.getTotal().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //建设场地征用及清理费
            cell = row.createCell(8);
            cell.setCellValue(project.getJscdzyjglf()==null?0:project.getJscdzyjglf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目建设管理费
            cell = row.createCell(9);
            cell.setCellValue(project.getXmjsglf()==null?0:project.getXmjsglf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目管理经费
            cell = row.createCell(10);
            cell.setCellValue(project.getXmgljf()==null?0:project.getXmgljf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //工程监理费
            cell = row.createCell(11);
            cell.setCellValue(project.getGcjlf()==null?0:project.getGcjlf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //施工费
            cell = row.createCell(12);
            cell.setCellValue(project.getSgf()==null?0:project.getSgf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目前期工作费
            cell = row.createCell(13);
            cell.setCellValue(project.getXmqqgzf()==null?0:project.getXmqqgzf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //勘察费
            cell = row.createCell(14);
            cell.setCellValue(project.getKcf()==null?0:project.getKcf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //设计费
            cell = row.createCell(15);
            cell.setCellValue(project.getSjf()==null?0:project.getSjf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //设计文件评审费
            cell = row.createCell(16);
            cell.setCellValue(project.getSjwjpsf()==null?0:project.getSjwjpsf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //项目后评价费
            cell = row.createCell(17);
            cell.setCellValue(project.getXmhpjf()==null?0:project.getXmhpjf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //建设期贷款利息
            cell = row.createCell(18);
            cell.setCellValue(project.getJsqdklx()==null?0:project.getJsqdklx().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //工程保险费
            cell = row.createCell(19);
            cell.setCellValue(project.getGcbxf()==null?0:project.getGcbxf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //施工过程造价咨询及竣工结算审核费
            cell = row.createCell(20);
            cell.setCellValue(project.getJgjsshf()==null?0:project.getJgjsshf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //生产准备费
            cell = row.createCell(21);
            cell.setCellValue(project.getSczbf()==null?0:project.getSczbf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //带电作业费
            cell = row.createCell(22);
            cell.setCellValue(project.getDdzyf()==null?0:project.getDdzyf().doubleValue());
            cell.setCellStyle(styleMap.get(1));
            //加工次
            cell = row.createCell(23);
            cell.setCellValue(project.getJgchjje()==null?0:project.getJgchjje().doubleValue());
            cell.setCellStyle(styleMap.get(1));


            //工程类别
            cell = row.createCell(24);
            cell.setCellValue(project.getCreatetype());
            cell.setCellStyle(styleMap.get(0));
            //是否入库
            cell = row.createCell(25);
            cell.setCellValue(project.getIsconfirm());
            cell.setCellStyle(styleMap.get(0));
            //工程书日期
            cell = row.createCell(26);
            cell.setCellValue(project.getBookdate());
            cell.setCellStyle(styleMap.get(0));
            //导入文件名称
            cell = row.createCell(27);
            cell.setCellValue(project.getFilename());
            cell.setCellStyle(styleMap.get(0));
            //入库确认时间
            cell = row.createCell(28);
            InfoProjectbudgetJs infoProjectbudget = project;
            System.out.println(Objects.isNull(cell));
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//            cell.setCellValue(Strings.isBlank(project.getConfirmdate().toString())?null:project.getConfirmdate());
            cell.setCellValue(project.getConfirmdate() == null ? " " : formatter.format(project.getConfirmdate()));
            cell.setCellStyle(styleMap.get(0));

        }

        String fileName = "333.xls";
        SystemExportTools.commonDownload(request, response, workbook, fileName);
    }

    /**
     * 结算环节导出--用于项目编号绑定
     *
     * @param request
     * @param response
     * @throws IOException
     */
    @GetMapping("agencyStyleExport4proJsnum")
    public void agencyStyleExport4proJsnum(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String isconfirm = request.getParameter("isconfirm");
        // 创建sheet
        String templatePath = templatePaths + "/projectBudget4num.xls";
        FileInputStream fileInputStream = new FileInputStream(templatePath);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFFont font = workbook.createFont();

        Map<Integer, HSSFCellStyle> styleMap = SystemExportTools.indexStyle(workbook, font);
        Map<Integer, HSSFCellStyle> styleMapRed = SystemExportTools.indexStyleRed(workbook, font);

        //创建行
        HSSFSheet sheet = workbook.getSheetAt(0);
        InfoProjectbudgetJsExample infoProjectbudgetJsExample = new InfoProjectbudgetJsExample();
//        infoProjectbudgetCsExample.createCriteria().andIsconfirmEqualTo(Integer.valueOf(isconfirm));
        List<InfoProjectbudgetJs> infoProjectbudgetJss = infoProjectbudgetJsService.selectByExample(infoProjectbudgetJsExample);

        //常见单元格并写入数据
        for (int i = 0; i < infoProjectbudgetJss.size(); i++) {
            Row row = sheet.createRow((i + 1));
            InfoProjectbudgetJs project = infoProjectbudgetJss.get(i);
            //创建时间
            Cell cell = row.createCell(0);
            cell.setCellValue(project.getPronum());
            cell.setCellStyle(styleMap.get(0));
            //工程类别（1基建类2业扩类）
            cell = row.createCell(1);
            cell.setCellValue(project.getProname());
            cell.setCellStyle(styleMap.get(0));
            //工程名称
            cell = row.createCell(2);
            cell.setCellValue(project.getType());
            cell.setCellStyle(styleMap.get(0));
            //工程书日期
            cell = row.createCell(3);
            cell.setCellValue(project.getBookdate());
            cell.setCellStyle(styleMap.get(0));
            //导入文件名称
            cell = row.createCell(4);
            cell.setCellValue(project.getFilename());
            cell.setCellStyle(styleMap.get(0));
            //是否入库
            cell = row.createCell(5);
            cell.setCellValue(project.getIsconfirm());
            cell.setCellStyle(styleMap.get(0));
            //入库确认时间
            cell = row.createCell(6);
            InfoProjectbudgetJs infoProjectbudgetJs = project;
            System.out.println(Objects.isNull(cell));
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//            cell.setCellValue(Strings.isBlank(project.getConfirmdate().toString())?null:project.getConfirmdate());
            cell.setCellValue(project.getConfirmdate() == null ? " " : formatter.format(project.getConfirmdate()));
            cell.setCellStyle(styleMap.get(0));
            //所属环节
            cell = row.createCell(7);
            cell.setCellValue("结算");
            cell.setCellStyle(styleMap.get(0));

        }

        String fileName = "111.xls";
        SystemExportTools.commonDownload(request, response, workbook, fileName);
    }

    /**
     * 综合比对详情导出（单个）
     *
     * @param request
     * @param response
     * @throws IOException
     */
    @GetMapping("agencyStyleExport4anaSingle")
    public void agencyStyleExport4anaSingle(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String id = request.getParameter("id");
        System.err.println(id);
        // 创建sheet
        String templatePath = templatePaths + "/compare.xls";
        FileInputStream fileInputStream = new FileInputStream(templatePath);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFFont font = workbook.createFont();

        Map<Integer, HSSFCellStyle> styleMap = SystemExportTools.indexStyle(workbook, font);
        Map<Integer, HSSFCellStyle> styleMapRed = SystemExportTools.indexStyleRed(workbook, font);


        InfoProjectbudget infoProjectbudget = infoProjectbudgetService.selectByPrimaryKey(id);
        InfoProjectbudgetCs infoProjectbudgetCs = null;
        InfoProjectbudgetJs infoProjectbudgetJs = null;
//        String proname = infoProjectbudget.getProname();
        String pronum = infoProjectbudget.getPronum();

        //创建行
        HSSFSheet sheet = workbook.getSheetAt(0);
        int sheetIndex = workbook.getSheetIndex(sheet); // 获取工作表的索引
        workbook.setSheetName(sheetIndex, pronum); // 设置新的名称

        InfoProjectbudgetCsExample infoProjectbudgetExample = new InfoProjectbudgetCsExample();
        infoProjectbudgetExample.createCriteria().andPronumEqualTo(pronum);
        List<InfoProjectbudgetCs> infoProjectbudgetCsList = infoProjectbudgetCsService.selectByExample(infoProjectbudgetExample);
        if (infoProjectbudgetCsList != null && infoProjectbudgetCsList.size() > 0) {
            infoProjectbudgetCs = infoProjectbudgetCsList.get(0);
        }


        InfoProjectbudgetJsExample infoProjectbudgetJsExample = new InfoProjectbudgetJsExample();
        infoProjectbudgetJsExample.createCriteria().andPronumEqualTo(pronum);
        List<InfoProjectbudgetJs> infoProjectbudgetJsList = infoProjectbudgetJsService.selectByExample(infoProjectbudgetJsExample);
        if (infoProjectbudgetJsList != null && infoProjectbudgetJsList.size() > 0) {
            infoProjectbudgetJs = infoProjectbudgetJsList.get(0);
        }

        createLine(sheet, styleMap, "建筑工程费", 1, infoProjectbudget.getJzgcf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getJzgcf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getJzgcf());

        createLine(sheet, styleMap, "安装工程费", 2, infoProjectbudget.getAzgcf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getAzgcf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getAzgcf());

        createLine(sheet, styleMap, "设备购置费", 3, infoProjectbudget.getSbgzf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getSbgzf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getSbgzf());

        createLine(sheet, styleMap, "其他费", 4, infoProjectbudget.getQtf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getQtf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getQtf());

        createLine(sheet, styleMap, "总投资", 5, infoProjectbudget.getTotal(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getTotal(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getTotal());

        createLine(sheet, styleMap, "建设场地征用及清理费", 6, infoProjectbudget.getJscdzyjglf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getJscdzyjglf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getJscdzyjglf());

        createLine(sheet, styleMap, "项目建设管理费", 7, infoProjectbudget.getXmjsglf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getXmjsglf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getXmjsglf());

        createLine(sheet, styleMap, "项目管理经费", 8, infoProjectbudget.getXmgljf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getXmgljf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getXmgljf());

        createLine(sheet, styleMap, "工程监理费", 9, infoProjectbudget.getGcjlf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getGcjlf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getGcjlf());

        createLine(sheet, styleMap, "施工费", 10, infoProjectbudget.getSgf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getSgf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getSgf());

        createLine(sheet, styleMap, "项目前期工作费", 11, infoProjectbudget.getXmqqgzf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getXmqqgzf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getXmqqgzf());

        createLine(sheet, styleMap, "勘察费", 12, infoProjectbudget.getKcf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getKcf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getKcf());

        createLine(sheet, styleMap, "设计费", 13, infoProjectbudget.getSjf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getSjf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getSjf());

        createLine(sheet, styleMap, "设计文件评审费", 14, infoProjectbudget.getSjwjpsf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getSjwjpsf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getSjwjpsf());

        createLine(sheet, styleMap, "项目后评价费", 15, infoProjectbudget.getXmhpjf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getXmhpjf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getXmhpjf());

        createLine(sheet, styleMap, "建设期贷款利息", 16, infoProjectbudget.getJsqdklx(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getJsqdklx(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getJsqdklx());

        createLine(sheet, styleMap, "工程保险费", 17, infoProjectbudget.getGcbxf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getGcbxf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getGcbxf());

        createLine(sheet, styleMap, "施工过程造价咨询及竣工结算审核费", 18, infoProjectbudget.getJgjsshf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getJgjsshf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getJgjsshf());

        createLine(sheet, styleMap, "生产准备费", 19, infoProjectbudget.getSczbf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getSczbf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getSczbf());

        createLine(sheet, styleMap, "带电作业费", 20, infoProjectbudget.getDdzyf(),
                infoProjectbudgetCs == null ? null : infoProjectbudgetCs.getDdzyf(), infoProjectbudgetJs == null ? null : infoProjectbudgetJs.getDdzyf());

//        ObjectMapper objectMapper = new ObjectMapper();
//        Map<String, Object> map = objectMapper.convertValue(infoProjectbudget1, Map.class);
//        // 使用任意一种方法遍历Map
//        for (Map.Entry<String, Object> entry : map.entrySet()) {
//            String key = entry.getKey();
//            Object value = entry.getValue();
//            System.out.println("Key: " + key + ", Value: " + value);
//        }

        String fileName = "333.xls";
        SystemExportTools.commonDownload(request, response, workbook, fileName);
    }

    private void createLine(HSSFSheet sheet, Map<Integer, HSSFCellStyle> styleMap, String proname, Integer rownum, BigDecimal kyvalue, BigDecimal csvalue, BigDecimal jsvalue) {

        String kycscha = "/";
        String csresult = "/";
        String csjscha = "/";
        String jsresult = "/";
        String kyjscha = "/";

        if (csvalue != null) {
            if (jsvalue != null) {
                kycscha = csvalue.subtract(kyvalue).toString();
                csresult = csvalue.toString();
                csjscha = jsvalue.subtract(csvalue).toString();
                jsresult = jsvalue.toString();
                kyjscha = jsvalue.subtract(kyvalue).toString();
            } else {
                kycscha = csvalue.subtract(kyvalue).toString();
                csresult = csvalue.toString();
            }
        }

        //封装数据行
        Row row = sheet.createRow(rownum);
        //费用名称
        Cell cell = row.createCell(0);
        cell.setCellValue(proname);
        cell.setCellStyle(styleMap.get(0));
        //可研
        cell = row.createCell(1);
        cell.setCellValue(kyvalue.toString());
        cell.setCellStyle(styleMap.get(0));
        //可研与初设差
        cell = row.createCell(2);
        cell.setCellValue(kycscha);
        cell.setCellStyle(styleMap.get(0));
        //初设
        cell = row.createCell(3);
        cell.setCellValue(csresult);
        cell.setCellStyle(styleMap.get(0));
        //可研与初设差
        cell = row.createCell(4);
        cell.setCellValue(csjscha);
        cell.setCellStyle(styleMap.get(0));
        //结算
        cell = row.createCell(5);
        cell.setCellValue(jsresult);
        cell.setCellStyle(styleMap.get(0));
        //可研与结算差
        cell = row.createCell(6);
        cell.setCellValue(kyjscha);
        cell.setCellStyle(styleMap.get(0));
    }
}
